package com.yulian.web_api.common;

  
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;

import com.healthmarketscience.sqlbuilder.CustomSql;
import com.healthmarketscience.sqlbuilder.InCondition;
import com.healthmarketscience.sqlbuilder.SelectQuery;
import com.yulian.repository.CommonRepositoryImpl; 
import com.yulian.util.AESUtils;
import com.yulian.util.CommonUtils;
import com.yulian.util.RSAUtils;

import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.ext.auth.User;
import io.vertx.pgclient.PgPool;
import io.vertx.sqlclient.Row;
import io.vertx.sqlclient.RowSet;
import io.vertx.sqlclient.Tuple; 

/**
 * 提供一些共用的筛选下拉框数据接口，比如供应链商品聚集区、商品一级分类、小二分类、品种、聚合商品名、供应商名、销售地区、供应链商品名、挂牌商品名
 * @author 余大勇 汪俊峰 周恒宇 刘涛 王若禹 谢大萌
 *
 */
public class CommonManager {
	final InternalLogger logger = Log4JLoggerFactory.getInstance(CommonManager.class);
	CommonRepositoryImpl commonRepository = new CommonRepositoryImpl();  
	 //移动业务管理类里，
	 public Future<JsonObject> deletePreparePickupPlan(PgPool client, JsonObject bodyJSObj,  User user)  {
	        Promise<JsonObject> promise = Promise.promise();
	        int user_id = user.attributes().getJsonObject("accessToken").getInteger("userId");
	        //String create_user = user.attributes().getJsonObject("accessToken").getString("username"); 
	        // 验证必须传递的字段
	 		List<String>  must_fields = Arrays.asList("prepare_pickup_plan_code");
	 		JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
	 		if (must != null) {
	 			promise.complete(must);  
	 			return promise.future();
	 		}    
	        String prepare_pickup_plan_code = bodyJSObj.getString("prepare_pickup_plan_code");   
	        this.commonRepository.getCon(client).onSuccess(conn -> {  
	            String delete_prepare_pickup_plan = "select delete_prepare_pickup_plan($1,$2) ";
	            this.commonRepository.callDBFunction(conn, delete_prepare_pickup_plan, Tuple.of(prepare_pickup_plan_code, user_id), "/web/prepare_pickup_plan/delete", "失效供应链商品聚集区预提计划单" )  
	            .onSuccess(resultJSObj -> { 
	                // 写入操作日志
	                this.commonRepository.writePlatformLog(conn, user_id, "失效", "/web/prepare_pickup_plan/delete", bodyJSObj, resultJSObj, "")
	                .onComplete(ar3 -> { 
	                    promise.complete(resultJSObj);
	                });
	            }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "/web/prepare_pickup_plan/delete", "失效供应链商品聚集区预提计划单发生错误")));  
	        }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, "/web/prepare_pickup_plan/delete", "数据库连接失败"))); 
	        return promise.future();
	    }  
	
	 /**
     * 该方法实现接收web前端通过body传入的参数，调用公共类的CommonRepositoryImpl方法实现数据的 准入/不准入 。 
     * @param client    连接池对象
     * @param config    配置对象
     * @param bodyJSObj body参数对象
     * @param user      操作用户
     * @return 操作的结果
     * @throws Exception 
     */
    public Future<JsonObject> admit(PgPool client, JsonObject bodyJSObj,String table_name, String msg, String url, User user)  {
        Promise<JsonObject> promise = Promise.promise();
        int user_id = user.attributes().getJsonObject("accessToken").getInteger("userId");
        //String create_user = user.attributes().getJsonObject("accessToken").getString("username"); 
        // 验证必须传递的字段
 		List<String>  must_fields = Arrays.asList("id_list");
 		JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
 		if (must != null) {
 			promise.complete(must);  
 			return promise.future();
 		}   
 		// 验证数值字段
		List<String>  numeric_fields = Arrays.asList("is_admit");
		JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
		if (numeric != null) {
			promise.complete(numeric);  
			return promise.future();
		} 
        // 获取记录的id列表
        String id_list = bodyJSObj.getString("id_list");  
        // 准入 is_admit = 1 或 不准入 is_admit = 0
        int is_admit = bodyJSObj.getInteger("is_admit");
        this.commonRepository.getCon(client).onSuccess(conn -> { 
    		String admit_title ="";
        	if(is_admit == 0)
        		admit_title = "不准入";
        	else
        		admit_title = "准入";
            String admit_partner_loss_ratio_sql = "update "+ table_name + " set is_admit = $1, update_user = $2 where id= any(array" + id_list + ") RETURNING id ";
            this.commonRepository.admitOrDelete(conn, admit_partner_loss_ratio_sql, Tuple.of(is_admit, user_id), url, admit_title+msg )  
            .onSuccess(resultJSObj -> { 
                // 写入操作日志
                this.commonRepository.writePlatformLog(conn, user_id, url, bodyJSObj, resultJSObj, "")
                .onComplete(ar3 -> { 
                    promise.complete(resultJSObj);
                });
            }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, url, "准入/不准入"+msg+"发生错误")));  
        }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, url, "数据库连接失败"))); 
        return promise.future();
    }  
    
    /**
     * 该方法实现接收web前端通过body传入的参数，调用公共类的CommonRepositoryImpl方法实现数据的 准入/不准入 。 
     * @param client    连接池对象
     * @param config    配置对象
     * @param bodyJSObj body参数对象
     * @param user      操作用户
     * @return 操作的结果
     * @throws Exception 
     */
    public Future<JsonObject> admitWithAdmitTime(PgPool client, JsonObject bodyJSObj,String table_name, String msg, String url, User user)  {
        Promise<JsonObject> promise = Promise.promise();
        int user_id = user.attributes().getJsonObject("accessToken").getInteger("userId");
        //String create_user = user.attributes().getJsonObject("accessToken").getString("username"); 
        // 验证必须传递的字段
 		List<String>  must_fields = Arrays.asList("id_list");
 		JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
 		if (must != null) {
 			promise.complete(must);  
 			return promise.future();
 		}   
 		// 验证数值字段
		List<String>  numeric_fields = Arrays.asList("is_admit");
		JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
		if (numeric != null) {
			promise.complete(numeric);  
			return promise.future();
		} 
        // 获取记录的id列表
        String id_list = bodyJSObj.getString("id_list");  
        // 准入 is_admit = 1 或 不准入 is_admit = 0
        int is_admit = bodyJSObj.getInteger("is_admit");
        this.commonRepository.getCon(client).onSuccess(conn -> { 
    		String admit_title ="";
        	if(is_admit == 0)
        		admit_title = "不准入";
        	else
        		admit_title = "准入";
            String admit_partner_loss_ratio_sql = "update "+ table_name + " set is_admit = $1, update_user = $2, admit_time=now() where id= any(array" + id_list + ") RETURNING id ";
            this.commonRepository.admitOrDelete(conn, admit_partner_loss_ratio_sql, Tuple.of(is_admit, user_id), url, admit_title+msg )  
            .onSuccess(resultJSObj -> { 
                // 写入操作日志
                this.commonRepository.writePlatformLog(conn, user_id, url, bodyJSObj, resultJSObj, "")
                .onComplete(ar3 -> { 
                    promise.complete(resultJSObj);
                });
            }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, url, "准入/不准入"+msg+"发生错误")));  
        }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, url, "数据库连接失败"))); 
        return promise.future();
    }  
    
    /**
     * 该方法实现接收web前端通过body传入的参数，调用公共类的CommonRepositoryImpl方法实现数据的 生效/失效 。 
     * @param client
     * @param bodyJSObj
     * @param table_name  数据表名
     * @param msg
     * @param url
     * @param user
     * @return
     */ 
    public Future<JsonObject> delete(PgPool client, JsonObject bodyJSObj, String table_name, String msg, String url,  User user)  {
        Promise<JsonObject> promise = Promise.promise();
        int user_id = user.attributes().getJsonObject("accessToken").getInteger("userId");
        //String create_user = user.attributes().getJsonObject("accessToken").getString("username"); 
        // 验证必须传递的字段
 		List<String>  must_fields = Arrays.asList("id_list");
 		JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
 		if (must != null) {
 			promise.complete(must);  
 			return promise.future();
 		}   
 		// 验证数值字段
		List<String>  numeric_fields = Arrays.asList("is_delete");
		JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
		if (numeric != null) {
			promise.complete(numeric);  
			return promise.future();
		}  
		// 获取记录的id列表
        String id_list = bodyJSObj.getString("id_list"); 
		// 生效 is_delete = 0 或 失效 is_delete = 1
        int is_delete = bodyJSObj.getInteger("is_delete");   
        this.commonRepository.getCon(client).onSuccess(conn -> {
        	String delete_title ="";
        	if(is_delete == 0)
        		delete_title = "生效";
        	else
        		delete_title = "失效";
            String admit_partner_loss_ratio_sql = "update "+ table_name + " set is_delete = $1, update_user = $2 where id= any(array" + id_list + ") RETURNING id ";
            this.commonRepository.insertOrUpdate(conn, admit_partner_loss_ratio_sql,Tuple.of(is_delete, user_id),url, delete_title+msg ) 
            .onSuccess(resultJSObj -> { 
                // 写入操作日志
                this.commonRepository.writePlatformLog(conn, user_id, url, bodyJSObj, resultJSObj, "")
                .onComplete(ar3 -> { 
                    promise.complete(resultJSObj);
                });
            }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, url, "生效/失效"+msg+"发生错误"))); 
        }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, url, "数据库连接失败"))); 
        return promise.future();
    }  
    
    /** 
     * 该方法实现接收web前端通过body传入的参数，调用公共类的CommonRepositoryImpl方法实现数据的生效/失效，准入或不准入 。
     * 通过含参数的Json对象bodyJSObj，通过method字段获取操作类型，分别是  delete 。
     * 1) 操作类型为 delete，逻辑删除一条记录。
     * 2) 操作类型为 admit，准入或不准入一条记录。   
     * @param client    连接池对象
     * @param config    配置对象
     * @param bodyJSObj body参数对象
     * @param user      操作用户
     * @return 操作的结果
     * @throws Exception 
     */
    public Future<JsonObject> admitOrDelete(PgPool client, JsonObject bodyJSObj, String table_name, String msg, String url,  User user)  {
        Promise<JsonObject> promise = Promise.promise();
        int user_id = user.attributes().getJsonObject("accessToken").getInteger("userId");
        //String create_user = user.attributes().getJsonObject("accessToken").getString("username"); 
        // 验证必须传递的字段
 		List<String>  must_fields = Arrays.asList("method",  "id_list");
 		JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
 		if (must != null) {
 			promise.complete(must);  
 			return promise.future();
 		}  
        // 获取操作类型字段的值 
        String method = bodyJSObj.getString("method");
        // 获取记录的id列表
        String id_list = bodyJSObj.getString("id_list"); 
        
        this.commonRepository.getCon(client).onSuccess(conn -> {
        	if (method.equals("delete")) {
        		// 验证数值字段
        		List<String>  numeric_fields = Arrays.asList("is_delete");
        		JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        		if (numeric != null) {
        			promise.complete(numeric);  
        			return;
        		} 
        		// 生效 is_delete = 0 或 失效 is_delete = 1
                int is_delete = bodyJSObj.getInteger("is_delete"); 
        		String delete_title ="";
            	if(is_delete == 0)
            		delete_title = "生效";
            	else
            		delete_title = "失效";
                String admit_partner_loss_ratio_sql = "update "+ table_name + " set is_delete = $1, update_user = $2 where id= any(array" + id_list + ") ";
                this.commonRepository.insertOrUpdate(conn, admit_partner_loss_ratio_sql,Tuple.of(is_delete, user_id),url, delete_title+msg ) 
                .onSuccess(resultJSObj -> { 
                    // 写入操作日志
                    this.commonRepository.writePlatformLog(conn, user_id, url, bodyJSObj, resultJSObj, "")
                    .onComplete(ar3 -> { 
                        promise.complete(resultJSObj);
                    });
                }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, url, "生效/失效"+msg+"发生错误")));  
            } else if (method.equals("admit")) {
        		// 验证数值字段
        		List<String>  numeric_fields = Arrays.asList("is_admit");
        		JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        		if (numeric != null) {
        			promise.complete(numeric);  
        			return;
        		} 
        		// 准入 is_admit = 1 或 不准入 is_admit = 0
                int is_admit = bodyJSObj.getInteger("is_admit"); 
        		String admit_title ="";
            	if(is_admit == 0)
            		admit_title = "不准入";
            	else
            		admit_title = "准入";
                String admit_partner_loss_ratio_sql = "update "+ table_name + " set is_admit = $1, update_user = $2 where id= any(array" + id_list + ") ";
                this.commonRepository.insertOrUpdate(conn, admit_partner_loss_ratio_sql,Tuple.of(is_admit, user_id),url, admit_title+msg ) 
                .onSuccess(resultJSObj -> { 
                    // 写入操作日志
                    this.commonRepository.writePlatformLog(conn, user_id, url, bodyJSObj, resultJSObj, "")
                    .onComplete(ar3 -> { 
                        promise.complete(resultJSObj);
                    });
                }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, url, "准入/不准入"+msg+"发生错误")));  
            } 
        }).onFailure(err -> promise.fail(CommonUtils.createExceptionJsonObjectStr(err, url, "数据库连接失败"))); 
        return promise.future();
    }  
    
	
	/**
	 * 为查询页面下拉筛选框提供数据，数据应是数据表里存在的数据，该方法构建sql语句，从数据表里查询存在的某个字段的编码
	 * @param search_type 搜索类型
	 * @param queryJS 参数对象
	 * @param table_name 数据表名
	 * @return
	 */
    public String createSqlStr(String search_type, JsonObject queryJS, String table_name) {
        // define SelectQuery object
        SelectQuery sq = new SelectQuery();
        if (search_type.equals("get_goods_gather_area")) {
            // 获取table_name表中含有的供应链聚集区编码
            sq.addCustomColumns(new CustomSql("distinct goods_gather_area_id")).addCustomFromTable(table_name);
        } else if (search_type.equals("get_class_lv1")) {
            // 获取table_name表中含有的商品一级分类编码
            sq.addCustomColumns(new CustomSql("distinct class_lv1_id")).addCustomFromTable(table_name);
        }else if (search_type.equals("get_class_lv2")) {
            // 获取table_name表中含有的商品小二分类编码
            sq.addCustomColumns(new CustomSql("distinct class_lv2_id")).addCustomFromTable(table_name);
        }else if (search_type.equals("get_variety")) {
            // 获取table_name表中含有的商品品种编码
            sq.addCustomColumns(new CustomSql("distinct variety_id")).addCustomFromTable(table_name);
        }else if (search_type.equals("get_aggregation")) {
            // 获取table_name表中含有的聚合商品编码
            sq.addCustomColumns(new CustomSql("distinct aggregation_id")).addCustomFromTable(table_name);
        }else if (search_type.equals("get_supply_chain_goods")) {
            // 获取table_name表中含有的供应链商品编码
            sq.addCustomColumns(new CustomSql("distinct supply_chain_goods_id")).addCustomFromTable(table_name);
        }else if (search_type.equals("get_list_goods")) {
            // 获取table_name表中含有的挂牌商品编码
            sq.addCustomColumns(new CustomSql("distinct list_goods_id")).addCustomFromTable(table_name);
        }else if (search_type.equals("get_supply_chain_goods_grade")) {
            // 获取table_name表中含有的供应链商品等级编码
            sq.addCustomColumns(new CustomSql("distinct supply_chain_goods_grade_id")).addCustomFromTable(table_name);
        }else if (search_type.equals("get_supplier")) {
            // 获取table_name表中含有的供应商编码
            sq.addCustomColumns(new CustomSql("distinct supplier_id")).addCustomFromTable(table_name);
        }else if (search_type.equals("get_supplier_type")) {
            // 获取table_name表中含有的供应商类型
            sq.addCustomColumns(new CustomSql("distinct supplier_type")).addCustomFromTable(table_name);
        } else if (search_type.equals("get_goods_sales_grade")) {
            // 获取table_name表中含有的等级编码
            sq.addCustomColumns(new CustomSql("distinct grade_id")).addCustomFromTable(table_name);
        } else if (search_type.equals("get_site")) {
            // 获取table_name表中含有的销售地区编码
            sq.addCustomColumns(new CustomSql("distinct site_id")).addCustomFromTable(table_name);
        }else if (search_type.equals("get_payment_entity")) {
            // 获取table_name表中含有的支付主体编码
            sq.addCustomColumns(new CustomSql("distinct legal_entity_id")).addCustomFromTable(table_name);
        }else if (search_type.equals("get_mainline_route")) {
            // 获取table_name表中含有的支付主体编码
            sq.addCustomColumns(new CustomSql("distinct mainline_route_id")).addCustomFromTable(table_name);
        }else if (search_type.equals("get_truck_type")) {
            // 获取table_name表中含有的支付主体编码
            sq.addCustomColumns(new CustomSql("distinct truck_type_id")).addCustomFromTable(table_name);
        }else if (search_type.equals("get_loss_type")) {
            // 获取table_name表中含有的报损类型
            sq.addCustomColumns(new CustomSql("distinct loss_type_name")).addCustomFromTable(table_name);
        }else if (search_type.equals("get_phone")) {
            // 获取table_name表中含有的报损类型
            sq.addCustomColumns(new CustomSql("distinct phone")).addCustomFromTable(table_name);
        }else if (search_type.equals("get_user_name")) {
            // 获取table_name表中含有的报损类型
            sq.addCustomColumns(new CustomSql("distinct user_name")).addCustomFromTable(table_name);
        }
        
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                // 传入value 是形如[c1, c2] 或者[] 的字符串数组
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 最后字符串形式为： c1','c2','c3 ;
                    // 添加 where  InCondtion 子句； 生成如下语句：  class_lv1_id in ('c1','c2','c3')
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }

            } catch (Exception e) {
                logger.info(e.getCause().getMessage());
            }
        }
        return sq.validate().toString();
    } 
    
    /**
	 *  获取AES Key
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getAesKey(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject responseObj = new JsonObject();
        String PUBLIC_KEY_CODE = bodyJSObj.getString("PUBLIC_KEY_CODE");
        String aes_key = "yunli";
        try {
			String en_aes_key = RSAUtils.encrypt(aes_key, PUBLIC_KEY_CODE);
			responseObj.put("en_aes_key", en_aes_key).put("code", 0);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			responseObj.put("msg", e.getMessage()).put("code", -1);
		}
        promise.complete(responseObj);
        return promise.future();
	}
	
	  /**
	 *  获取AES Key
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> postEnData(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject responseObj = new JsonObject();
        String en_data = bodyJSObj.getString("en_data");
        logger.info("en_data: {}",en_data);
        String aes_key = "ssshhhhhhhhhhh!!!!";
        try {
        	//方法1  返回值不能正常显示
        	byte[] de_data = AESUtils.dnCode(aes_key, en_data);
        	logger.info("de_data, {}",de_data);
        	String result_str = String.valueOf(de_data); 
        	
        	//方法2    报错 Cipher not initialized
        	//String result_str = AES2.decryptStrAES(en_data, aes_key);
        	
        	//方法3 报错
        	//String result_str = AESUtils.decryptByHexString(en_data, aes_key);
        	
        	//方法4  报错
//        	String result_str = AES.decrypt(en_data, aes_key);
        	
        	logger.info("result_str, {}",result_str);
        	responseObj.put("msg", "提交成功").put("code", 0).put("de_data", result_str);
        	
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			responseObj.put("msg", e.getMessage()).put("code", -1);
		}
        promise.complete(responseObj);
        return promise.future();
	}
	
	/**
	 *  获取有效的供应链商品聚集区名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getGoodsGatherArea(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证必须传递的字段,值可以为空
 		List<String>  must_fields = Arrays.asList("class_lv1_id");
 		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
 		if (must != null) {
 			promise.complete(must);  
 			return promise.future();
 		}  
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	/** 聚集区和一级分类的关系处理; 如果传入参数中有一级分类编码，适用于通过一级分类编码查询聚集区编码和名称；否则，直接查询所有值 **/ 
        	String sql = "";
        	Long class_lv1_id = bodyJSObj.getLong("class_lv1_id");   
        	if(class_lv1_id.equals(0L)) {
        		sql = "select distinct id, goods_gather_area_name, pinyin from goods_gather_area where is_delete = 0 ";
        	}else {
        		sql = "select distinct id, goods_gather_area_name, pinyin from goods_gather_area where class_lv1_id like '%"+class_lv1_id+"%'";
//        		sql = "select distinct id, goods_gather_area_name, pinyin from goods_gather_area where is_delete = 0 ";
        	}  
        	this.commonRepository.queryJsonArray(conn, sql, "getGoodsGatherArea") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getGoodsGatherArea", "获取供应链聚集区失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getGoodsGatherArea", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  为查询界面供应链商品聚集区筛选下拉框，提供有效的商品聚集区名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getGoodsGatherAreaQuery(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	JsonObject queryJSObj = new JsonObject(); 
            String table_name = bodyJSObj.getString("table_name"); 
            String sql = "select distinct id, goods_gather_area_name, pinyin from goods_gather_area where id = any(array(" + 
        	  createSqlStr("get_goods_gather_area", queryJSObj, table_name) +")) and is_delete = 0"; 
        	this.commonRepository.queryJsonArray(conn, sql, "getGoodsGatherAreaQuery") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getGoodsGatherAreaQuery", "获取商品一级分类失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getGoodsGatherAreaQuery", "数据库连接失败")));
        return promise.future();
	} 
	
	/**
	 *  获取有效的商品一级分类名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getClassLv1(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证数值字段，如果没有值，可以传 0
  		List<String>  must_fields = Arrays.asList("goods_gather_area_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		} 
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	Long goods_gather_area_id = bodyJSObj.getLong("goods_gather_area_id");
        	logger.info("goods_gather_area_id: {}", goods_gather_area_id);
            String sql = "";
            if (goods_gather_area_id.equals(0L)) {
                sql = "select distinct id, class_name from goods_class_lv1 where is_delete=0 and id > $1";
            } else {
                sql = "select distinct id, class_name from goods_class_lv1 where is_delete=0 and id = any(array( select cast( unnest(string_to_array(class_lv1_id,',')) as bigint ) from goods_gather_area where id = $1 and is_delete=0 ))";
            }
        	this.commonRepository.queryJsonArray(conn, sql, Tuple.of(goods_gather_area_id),  "getClassLv1") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getClassLv1", "获取商品一级分类失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getClassLv1", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  获取商品小二分类所属的商品一级分类名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getClassLv1OfClassLv2(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证数值字段，如果没有值，可以传 0
  		List<String>  must_fields = Arrays.asList("class_lv2_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		}
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	Long class_lv2_id = bodyJSObj.getLong("class_lv2_id");
            String sql = "select distinct id, class_name from goods_class_lv1 where id = any(array( select distinct class_lv1_id from goods_class_lv2 where id = $1 and is_delete=0 ))  and is_delete=0";
        	this.commonRepository.queryJsonArray(conn, sql, Tuple.of(class_lv2_id), "getClassLv1OfClassLv2") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getClassLv1OfClassLv2", "获取商品小二分类所属的商品一级分类失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getClassLv1OfClassLv2", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  获取有效的商品小二分类名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getClassLv2(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证数值字段，如果没有值，可以传 0
  		List<String>  must_fields = Arrays.asList("class_lv1_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		}
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	Long class_lv1_id = bodyJSObj.getLong("class_lv1_id");
            String sql = "";
            if (class_lv1_id.equals(0L)) {
                //如果没有先选一级分类，直接选小二分类，就获取所有的二级分类
                sql = "select distinct id, class_name from goods_class_lv2 where is_delete=0 and id > $1";
            } else {
                sql = "select distinct id, class_name from goods_class_lv2 where class_lv1_id = $1 and is_delete=0";
            }
        	this.commonRepository.queryJsonArray(conn, sql, Tuple.of(class_lv1_id), "getClassLv2") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getClassLv2", "获取商品小二分类失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getClassLv2", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  获取品种所属的商品小二分类名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getClassLv2OfVariety(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证数值字段，如果没有值，可以传 0
  		List<String>  must_fields = Arrays.asList("variety_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		}
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	 Long variety_id = bodyJSObj.getLong("variety_id");
             String sql = "select distinct id, class_name from goods_class_lv2 where id = any(array( select distinct class_lv2_id from goods_variety where id= $1 and is_delete=0 ))  and is_delete=0";
        	this.commonRepository.queryJsonArray(conn, sql, Tuple.of(variety_id), "getClassLv2OfVariety") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getClassLv2OfVariety", "获取品种所属的商品小二分类失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getClassLv2OfVariety", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  获取商品品种名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getVariety(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证数值字段，如果没有值，可以传 0
  		List<String>  must_fields = Arrays.asList("class_lv2_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		}
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	Long class_lv2_id = bodyJSObj.getLong("class_lv2_id");
            String sql = "";
            if (class_lv2_id.equals(0L)) {
                //如果没有先选小二分类，直接选品种，就获取所有品种
                sql = "select distinct id, class_name from goods_variety where is_delete=0 and id > $1";
            } else {
                sql = "select distinct id, class_name from goods_variety where class_lv2_id = $1 and is_delete=0";
            }
        	this.commonRepository.queryJsonArray(conn, sql, Tuple.of(class_lv2_id),  "getVariety") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getVariety", "获取商品品种失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getVariety", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  获取商品聚合商品所属品种名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getVarietyOfAggregation(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证数值字段，如果没有值，可以传 0
  		List<String>  must_fields = Arrays.asList("aggregation_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		}
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	 Long aggregation_id = bodyJSObj.getLong("aggregation_id"); 
             String sql = "select distinct id, class_name from goods_variety where id = any(array( select distinct variety_id from aggregation_goods where id= $1 and is_delete=0 ))  and is_delete=0";
        	this.commonRepository.queryJsonArray(conn, sql, Tuple.of(aggregation_id), "getVarietyOfAggregation") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getVarietyOfAggregation", "获取商品品种失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getVarietyOfAggregation", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  获取商品聚合商品名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getAggregation(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证数值字段，如果没有值，可以传 0
  		List<String>  must_fields = Arrays.asList("variety_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		}
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	 Long variety_id = bodyJSObj.getLong("variety_id");
             String sql = "";
             if (variety_id.equals(0L)) {
                 sql = "select distinct id, aggregation_name from aggregation_goods where is_delete=0 and id > $1";
             } else {
                 sql = "select distinct id, aggregation_name from aggregation_goods where variety_id = $1 and is_delete = 0 ";
             }
        	this.commonRepository.queryJsonArray(conn, sql, Tuple.of(variety_id), "getVariety") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getAggregation", "获取商品聚合商品失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getAggregation", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  获取供应链商品所属聚合商品名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getAggregationOfSupplyChainGoods(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证数值字段，如果没有值，可以传 0
  		List<String>  must_fields = Arrays.asList("supply_chain_goods_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		}
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	 Long supply_chain_goods_id = bodyJSObj.getLong("supply_chain_goods_id");  
             String sql = "select distinct id, aggregation_name from aggregation_goods where id = any(array( select distinct aggregation_id from goods_parameter where supply_chain_goods_id = $1 and is_delete=0 ))  and is_delete=0";
        	this.commonRepository.queryJsonArray(conn, sql, Tuple.of(supply_chain_goods_id),"getAggregationOfSupplyChainGoods") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getAggregationOfSupplyChainGoods", "获取供应链商品所属聚合商品失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getAggregationOfSupplyChainGoods", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  从商品池表获取供应链商品名称和编码 废弃
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getSupplyChainGoodsFromGoodsPool(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证数值字段，如果没有值，可以传 0
  		List<String>  must_fields = Arrays.asList("aggregation_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		}
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	Long aggregation_id = bodyJSObj.getLong("aggregation_id"); 
             String sql = "select distinct id, supply_chain_goods_name from goods_pool_loan_relation where aggregation_id = $1 and is_delete=0";
        	this.commonRepository.queryJsonArray(conn, sql, Tuple.of(aggregation_id), "getSupplyChainGoods") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getSupplyChainGoods", "获取供应链商品失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getSupplyChainGoods", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  从商品参数表获取供应链商品名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getSupplyChainGoods(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证数值字段，如果没有值，可以传 0
  		List<String>  must_fields = Arrays.asList("aggregation_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		}
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	Long aggregation_id = bodyJSObj.getLong("aggregation_id"); 
             String sql = "select distinct a.supply_chain_goods_id as id, b.supply_chain_goods_name from goods_parameter a left join supply_chain_goods b on (a.supply_chain_goods_id = b.id) where a.aggregation_id = $1 and a.is_delete=0";
        	this.commonRepository.queryJsonArray(conn, sql, Tuple.of(aggregation_id), "getSupplyChainGoods") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getSupplyChainGoods", "获取供应链商品失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getSupplyChainGoods", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  从商品池表获取供应链商品等级名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getSupplyChainGoodsGrade(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证数值字段，如果没有值，可以传 0
  		List<String>  must_fields = Arrays.asList("supply_chain_goods_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		}
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	Long supply_chain_goods_id = bodyJSObj.getLong("supply_chain_goods_id"); 
            String sql = "select distinct a.supply_chain_goods_grade_id as id, b.grade_name from goods_parameter a left join goods_grade b on (a.supply_chain_goods_grade_id = b.id) where a.supply_chain_goods_id = $1 and a.is_delete=0";
       	  	this.commonRepository.queryJsonArray(conn, sql, Tuple.of(supply_chain_goods_id), "getSupplyChainGoodsGrade") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getSupplyChainGoodsGrade", "获取供应链商品等级失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getSupplyChainGoodsGrade", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  从商品池表获取挂牌商品名称和编码 废弃
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getListGoodsFromGoodsPool(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证数值字段，如果没有值，可以传 0
  		List<String>  must_fields = Arrays.asList("supply_chain_goods_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		}
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	Long supply_chain_goods_id = bodyJSObj.getLong("supply_chain_goods_id"); 
             String sql = "select distinct list_goods_id, list_goods_name from goods_pool_loan_relation where supply_chain_goods_id = $1 and is_delete=0";
        	this.commonRepository.queryJsonArray(conn, sql,Tuple.of(supply_chain_goods_id), "getListGoodsFromGoodsPool") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getListGoodsFromGoodsPool", "获取挂牌商品失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getListGoodsFromGoodsPool", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  从商品参数表获取挂牌商品名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getListGoods(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证数值字段，如果没有值，可以传 0
  		List<String>  must_fields = Arrays.asList("supply_chain_goods_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		}
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	Long supply_chain_goods_id = bodyJSObj.getLong("supply_chain_goods_id"); 
             String sql = "select distinct a.list_goods_id as id, b.list_goods_name from goods_parameter a left join list_goods b on (a.list_goods_id = b.id) where a.supply_chain_goods_id = $1 and a.is_delete=0";
        	this.commonRepository.queryJsonArray(conn, sql, Tuple.of(supply_chain_goods_id),  "getListGoods") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getListGoods", "获取挂牌商品失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getListGoods", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  从商品池表获取挂牌商品等级名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getListGoodsGrade(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证数值字段，如果没有值，可以传 0
  		List<String>  must_fields = Arrays.asList("list_goods_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		}
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	Long list_goods_id = bodyJSObj.getLong("list_goods_id"); 
            String sql = "select distinct a.list_goods_grade_id as id, b.grade_name from goods_parameter a left join goods_grade b on (a.list_goods_grade_id = b.id) where a.list_goods_id = $1 and a.is_delete=0";
       	  	
            this.commonRepository.queryJsonArray(conn, sql, Tuple.of(list_goods_id), "getListGoods") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getListGoodsGrade", "获取挂牌商品等级失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getListGoodsGrade", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *   获取挂可售量包规类型名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getSalesPackageSpecType(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
             String sql = "select distinct id, type_name from sales_package_spec_type where  is_delete=0";
        	this.commonRepository.queryJsonArray(conn, sql, "getSalesPackageSpecType") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getSalesPackageSpecType", "获取可售量包规类型失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getSalesPackageSpecType", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *   获取计价方式名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getGoodsPriceMode(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
             String sql = "select distinct id, price_mode_name from goods_price_mode where  is_delete=0";
        	this.commonRepository.queryJsonArray(conn, sql, "getGoodsPriceMode") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getGoodsPriceMode", "获取计价方式失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getGoodsPriceMode", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *   获取商品规格名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getGoodsSpecification(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
             String sql = "select distinct id, specification_name from goods_specification where  is_delete=0";
        	this.commonRepository.queryJsonArray(conn, sql, "getGoodsSpecification") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getGoodsSpecification", "获取商品规格失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getGoodsSpecification", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *   获取可售量方案名称和编码,可售量方案应该单独有数据表
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getSalesQuantityScheme(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
             String sql = "select distinct id, sales_quantity_scheme_name from goods_pool_loan_relation where  is_delete=0";
        	this.commonRepository.queryJsonArray(conn, sql, "getSalesQuantityScheme") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getSalesQuantityScheme", "获取可售量方案失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getSalesQuantityScheme", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  获取供应商名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getSupplier(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
             String sql = "select distinct id, supplier_name from supplier_info where is_delete=0";
        	this.commonRepository.queryJsonArray(conn, sql, "getSupplier") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getSupplier", "获取供应商名称和编码失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getSupplier", "数据库连接失败")));
        return promise.future();
	}  
	
	/**
	 *  获取供应商等级名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getSupplierGrade(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
             String sql = "select distinct id, grade_name from supplier_grade where is_delete=0";
        	this.commonRepository.queryJsonArray(conn, sql, "getSupplierGrade") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getSupplierGrade", "获取供应商等级名称和编码失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getSupplierGrade", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  获取供链合伙人名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getSupplierChainPartner(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
             String sql = "select distinct id, partner_name from supply_chain_partner where is_delete = 0";
        	this.commonRepository.queryJsonArray(conn, sql, "getSupplierChainPartner") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getSupplierChainPartner", "获取供链合伙人名称和编码失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getSupplierChainPartner", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  获取年龄组名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getAgeGroup(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	String sql = "select distinct id, age_group_name from age_group where is_delete = 0";
        	this.commonRepository.queryJsonArray(conn, sql, "getAgeGroup") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getAgeGroup", "获取年龄组名称和编码失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getAgeGroup", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  获取从业年限名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getOperationPeriod(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
             String sql = "select distinct id, operation_period_name from operation_period where is_delete = 0";
        	this.commonRepository.queryJsonArray(conn, sql, "getOperationPeriod") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getOperationPeriod", "获取从业年限名称和编码失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getOperationPeriod", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  获取账号类型名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getAccountType(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	String sql = "SELECT distinct id, type_name  from account_type where is_delete=0"; 
        	this.commonRepository.queryJsonArray(conn, sql, "getAccountType") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getAccountType", "获取账号类型名称和编码失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getAccountType", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  获取开票类型名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getInvoiceType(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	String sql = "select distinct id, type_name from invoice_type where is_delete=0"; 
        	this.commonRepository.queryJsonArray(conn, sql, "getInvoiceType") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getInvoiceType", "获取开票类型名称和编码失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getInvoiceType", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  获取开票周期名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getInvoiceCycle(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	String sql = "select distinct id, cycle_name from invoice_cycle where is_delete=0"; 
        	this.commonRepository.queryJsonArray(conn, sql, "getInvoiceCycle") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getInvoiceCycle", "获取开票周期名称和编码失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getInvoiceCycle", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  为查询界面商品销量等级筛选下拉框，提供有效的商品销量等级名称和编码
	 *  获取信息表 table_name 中含有的供应商
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getGoodsSalesGrade(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise(); 
        this.commonRepository.getCon(pool).onSuccess(conn -> {  
        	String sql = "select distinct id, grade_name from goods_sales_grade where is_delete=0";  
        	this.commonRepository.queryJsonArray(conn, sql, "getGoodsSalesGrade") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getGoodsSalesGrade", "获取商品销量等级失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getGoodsSalesGrade", "数据库连接失败")));
        return promise.future();
	} 
	
	/**
	 *  为新增界面原料计价模式筛选下拉框，提供有效的原料计价模式名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getMaterialPriceMode(PgPool pool) {
        Promise<JsonObject> promise = Promise.promise(); 
        this.commonRepository.getCon(pool).onSuccess(conn -> {  
        	String sql = "select distinct id, price_mode_name  from material_price_mode where is_delete=0";  
        	this.commonRepository.queryJsonArray(conn, sql, "getGoodsSalesGrade") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getGoodsSalesGrade", "获取原料计价模式失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getGoodsSalesGrade", "数据库连接失败")));
        return promise.future();
	} 
	/**
	 *  为新增界面干线运输标准线路名筛选下拉框，提供有效的干线运输标准线路名名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getMainlineRoute(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise(); 
        this.commonRepository.getCon(pool).onSuccess(conn -> {  
        	String sql = "select distinct id, mainline_route_name  from mainline_route where is_delete=0";  
        	this.commonRepository.queryJsonArray(conn, sql, "getGoodsSalesGrade") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getMainlineRoute", "获取干线运输标准线路名失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getMainlineRoute", "数据库连接失败")));
        return promise.future();
	} 
	
	/**
	 *  获取有效的角色名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getRole(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise(); 
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	String sql = "SELECT distinct id, role_name  FROM roles where is_delete = 0";
        	this.commonRepository.queryJsonArray(conn, sql, "getRole") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getRole", "获取角色失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRole", "数据库连接失败")));
        return promise.future();
	} 
	
	/**
	 * 用于添加用户下的角色时，获取角色列表（排除某个用户已经选择的角色）
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getUnSelectedRole(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise(); 
        // 验证数值字段，如果没有值，可以传 0
  		List<String>  must_fields = Arrays.asList("user_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		}
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	Long user_id = bodyJSObj.getLong("user_id");
        	String sql = "select id,  role_name  from roles where is_delete=0 and id not in (select role_id from users_roles  where user_id = $1 )";
         	this.commonRepository.queryJsonArray(conn, sql, Tuple.of(user_id),  "getRole") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getUnSelectedRole", "获取角色失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getUnSelectedRole", "数据库连接失败")));
        return promise.future();
	} 
	
	/**
	 *  获取有效的菜单名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getMenu(PgPool pool) {
        Promise<JsonObject> promise = Promise.promise(); 
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	String sql = "select distinct  menu_code, menu_name from system_menu";
        	this.commonRepository.queryJsonArray(conn, sql, "getMenu") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getMenu", "获取菜单失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getMenu", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  获取有效的父级菜单名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getParentMenu(PgPool pool) {
        Promise<JsonObject> promise = Promise.promise(); 
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	String sql = "select distinct menu_code, menu_name from system_menu where is_button = 0";
        	this.commonRepository.queryJsonArray(conn, sql, "getParentMenu") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getParentMenu", "获取父级菜单失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getParentMenu", "数据库连接失败")));
        return promise.future();
	}
	/**
	 *  获取有效的销售地区名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getSite(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证必须传递的字段,值可以为空
  		List<String>  must_fields = Arrays.asList("big_area_id");
  		JsonObject must = this.commonRepository.verifyAllParamNumeric(bodyJSObj, must_fields);
  		if (must != null) {
  			promise.complete(must);  
  			return promise.future();
  		} 
  		Long big_area_id = bodyJSObj.getLong("big_area_id");
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	/** 获取销售地区 **/ 
        	String sql = "";
        	if(big_area_id.equals(0L)) {
        		sql = "select distinct id, site_name, pinyin from site where is_delete = 0 and id > $1";
        	}else {
        		sql = "select distinct id, site_name, pinyin from site where big_area_id = $1";
        	}  
        	this.commonRepository.queryJsonArray(conn, sql, Tuple.of(big_area_id), "getSite") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getSite", "获取销售地区失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getSite", "数据库连接失败")));
        return promise.future();
	} 
	
	 
	/**
     * 获取干线运输标准线路名称和编码
     *
     * @param pool
     * @return
     */
    public Future<JsonObject> getMainlineRoute(PgPool pool) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> {
            String sql = "select distinct id, mainline_route_name from mainline_route where is_delete=0";
            this.commonRepository.queryJsonArray(conn, sql, "getMainlineRoute")
            .onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getMainlineRoute", "获取干线运输标准线路名称和编码失败")));
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getMainlineRoute", "数据库连接失败")));
        return promise.future();
    }
	
	/*************
	 * 构造查询界面筛选下拉框的数据接口，主要是多页面共用的一些下拉框 
	 ************/
	
	
	
	 
	
	/**
	 *  为查询界面一级分类筛选下拉框，提供有效的商品一级分类名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getClassLv1Query(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> {
        	//方法1:获取供应商基本信息表含有的一级分类
        	JsonObject queryJSObj = new JsonObject();
            String goods_gather_area_id_list = bodyJSObj.getString("goods_gather_area_id_list");
            queryJSObj.put("goods_gather_area_id", goods_gather_area_id_list); 
            String table_name = bodyJSObj.getString("table_name");
        	//如果goods_gather_area_id_list的值为空，直接获取所有有效的一级分类，否则，获取信息表 table_name 中含有的一级分类
            String sql = "";
            if (goods_gather_area_id_list.equals("")) {
                sql = "select distinct id, class_name from goods_class_lv1 where is_delete=0";
            } else {
            	sql = "select distinct id, class_name from goods_class where id =  any(array(" + createSqlStr("get_class_lv1", queryJSObj, table_name) + "))  and is_delete = 0";
            }
        	this.commonRepository.queryJsonArray(conn, sql, "getClassLv1Query") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getClassLv1Query", "获取商品一级分类失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getClassLv1Query", "数据库连接失败")));
        return promise.future();
	} 
	
	/**
	 *  为查询界面小二分类筛选下拉框，提供有效的商品小二分类名称和编码
	 *  如果class_lv1_id_list的值为空，直接获取所有有效的小二分类，否则，获取信息表 table_name 中含有的小二分类
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getClassLv2Query(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	JsonObject queryJSObj = new JsonObject();
            String class_lv1_id_list = bodyJSObj.getString("class_lv1_id_list");
            String table_name = bodyJSObj.getString("table_name");
            queryJSObj.put("class_lv1_id", class_lv1_id_list); 
            //如果class_lv1_id_list的值为空，直接获取所有有效的小二分类，否则，获取信息表 table_name 中含有的小二分类
            String sql = "";
            if (class_lv1_id_list.equals("")) {
                sql = "select distinct id, class_name from goods_class_lv2 where is_delete=0";
            } else {
            	sql = "select id, class_name from goods_class_lv2 where id = any(array( " +
                		createSqlStr("get_class_lv2", queryJSObj, table_name) + " ))  and is_delete=0";
            }  
        	this.commonRepository.queryJsonArray(conn, sql, "getClassLv2Query") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getClassLv2Query", "获取商品小二分类失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getClassLv2Query", "数据库连接失败")));
        return promise.future();
	} 
	
	/**
	 *  为查询界品种筛选下拉框，提供有效的商品品种名称和编码
	 *  如果class_lv2_id_list的值为空，直接获取所有有效的品种，否则，获取信息表 table_name 中含有的品种
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getVarietyQuery(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	JsonObject queryJSObj = new JsonObject();
            String class_lv2_id_list = bodyJSObj.getString("class_lv2_id_list");
            String table_name = bodyJSObj.getString("table_name");
            queryJSObj.put("class_lv2_id", class_lv2_id_list); 
            //如果class_lv2_id_list的值为空，直接获取所有有效的品种，否则，获取信息表 table_name 中含有的品种
            String sql = "";
            if (class_lv2_id_list.equals("")) {
                sql = "select distinct id, class_name from goods_variety where is_delete=0";
            } else {
            	sql = "select id, class_name from goods_variety where id = any(array( " +
                		createSqlStr("get_variety", queryJSObj, table_name) + " )) and is_delete=0";
            }  
        	this.commonRepository.queryJsonArray(conn, sql, "getVarietyQuery") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getVarietyQuery", "获取商品品种失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getVarietyQuery", "数据库连接失败")));
        return promise.future();
	} 
	
	/**
	 *  为查询界面品种筛选下拉框，提供有效的商品品种名称和编码
	 *  如果class_lv2_id_list的值为空，直接获取所有有效的品种，否则，获取信息表 table_name 中含有的品种
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getAggregationQuery(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	JsonObject queryJSObj = new JsonObject();  
            String variety_id_list = bodyJSObj.getString("variety_id_list");
            queryJSObj.put("variety_id", variety_id_list);
            String table_name = bodyJSObj.getString("table_name");
            String sql = "";
            if (variety_id_list.equals("")) {
                sql = "select distinct id, aggregation_name from aggregation_goods where  is_delete=0";
            } else {
                sql = "select distinct id, aggregation_name from aggregation_goods where id = any(array( " + createSqlStr("get_aggregation", queryJSObj, table_name) + " )) and is_delete=0";
            } 
        	this.commonRepository.queryJsonArray(conn, sql, "getAggregationQuery") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getAggregationQuery", "获取聚合商品失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getAggregationQuery", "数据库连接失败")));
        return promise.future();
	}  
	 
	
	/**
	 *  为查询界面供应商筛选下拉框，提供有效的供应商名称和编码
	 *  获取信息表 table_name 中含有的供应商
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getSiteQuery(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	JsonObject queryJSObj = new JsonObject();   
            String table_name = bodyJSObj.getString("table_name");
            String sql = "";
            if(!table_name.equals("")) //用于除供应商基本信息页面外的其它页面中 供应商筛选下拉框
            	sql = "select distinct id, site_name, pinyin from site where id = any(array( " + createSqlStr("get_site", queryJSObj, table_name) + " )) and is_delete=0 ";
            else  //用于供应商基本信息页面 供应商筛选下拉框
            	sql = "select distinct id, site_name, pinyin from site where is_delete = 0";
            this.commonRepository.queryJsonArray(conn, sql, "getSupplierQuery") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getSiteQuery", "获取供应商失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getSiteQuery", "数据库连接失败")));
        return promise.future();
	} 
	/**
     * 为查询界面干线运输标准线路筛选下拉框，提供有效的干线运输标准线路等级名称和编码
     * 获取信息表table_name中含有的干线运输标准线路
     *
     * @param pool
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getMainlineRouteQuery(PgPool pool, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject queryJSObj = new JsonObject();
        String table_name = bodyJSObj.getString("table_name");
        if (table_name.equals("")) {
            promise.fail(CommonUtils.createExceptionJsonObjectStr(408, "参数不合法"));
            return promise.future();
        }
       
        this.commonRepository.getCon(pool).onSuccess(conn -> {
            String sql = "select distinct id, mainline_route_name from mainline_route where id = any(array( " +
            createSqlStr("get_mainline_route", queryJSObj, table_name) + " )) and is_delete = 0";
            this.commonRepository.queryJsonArray(conn, sql, "getMainlineRouteQuery")
            .onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getMainlineRouteQuery", "获取干线运输标准线路失败")));
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getMainlineRouteQuery", "数据库连接失败")));
        return promise.future();
    }

	
	/**
	 *  为查询界面供应商筛选下拉框，提供有效的供应商名称和编码
	 *  获取信息表 table_name 中含有的供应商
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getSupplierQuery(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	JsonObject queryJSObj = new JsonObject();   
            String table_name = bodyJSObj.getString("table_name");
            String sql = "";
            if(!table_name.equals("")) //用于除供应商基本信息页面外的其它页面中 供应商筛选下拉框
            	sql = "select distinct id, supplier_name from supplier_info where id = any(array( " + createSqlStr("get_supplier", queryJSObj, table_name) + " )) and is_delete=0 ";
            else  //用于供应商基本信息页面 供应商筛选下拉框
            	sql = "select distinct id, supplier_name from supplier_info where is_delete=0";
            this.commonRepository.queryJsonArray(conn, sql, "getSupplierQuery") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getSupplierQuery", "获取供应商失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getSupplierQuery", "数据库连接失败")));
        return promise.future();
	} 
	
	/**
	 *  为查询界面商品销量等级筛选下拉框，提供有效的商品销量等级名称和编码
	 *  获取信息表 table_name 中含有的供应商
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getGoodsSalesGradeQuery(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        JsonObject queryJSObj = new JsonObject();   
        String table_name = bodyJSObj.getString("table_name"); 
        if(table_name.equals("")) {
        	promise.fail(CommonUtils.createExceptionJsonObjectStr(408, "参数 不合法"));
            return promise.future();
        } 
        this.commonRepository.getCon(pool).onSuccess(conn -> {  
            String sql = "select distinct id, grade_name  from goods_sales_grade where id = any(array( " + createSqlStr("get_goods_sales_grade", queryJSObj, table_name) + " )) and is_delete=0 ";
            this.commonRepository.queryJsonArray(conn, sql, "getGoodsSalesGradeQuery") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getGoodsSalesGradeQuery", "获取商品销量等级失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getGoodsSalesGradeQuery", "数据库连接失败")));
        return promise.future();
	} 
	
	
	
	/**
	 *  为查询界面供应商类型筛选下拉框，提供有效的供应商类型名称和编码
	 *  获取信息表 table_name 中含有的供应商类型
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getSupplierTypeQuery(PgPool pool, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	JsonObject queryJSObj = new JsonObject();   
            String table_name = bodyJSObj.getString("table_name");
            String sql = "";
            if(!table_name.equals("")) //用于除供应商基本信息页面外的其它页面中 供应商类型筛选下拉框
            	sql = "select distinct supplier_type from supplier_info where supplier_type = any(array( " + createSqlStr("get_supplier_type", queryJSObj, table_name) + " )) and is_delete=0 ";
            else  //用于供应商基本信息页面 供应商类型筛选下拉框
            	sql = "select distinct supplier_type from supplier_info where is_delete = 0";
            JsonArray result_Array = new JsonArray();
            this.commonRepository.commonSelectRowsetBySql(conn, sql).onComplete(ar -> {
                if (ar.failed()) {
                	promise.complete(CommonUtils.createExceptionJsonObject(ar, "getSupplierTypeQuery", "获取准入类型失败"));
                } else {
                    RowSet<Row> rows = ar.result();
                    rows.forEach(item -> {
                    	JsonObject json = new JsonObject();
                        json.put("supplier_type", item.getValue("supplier_type"));
                        JsonObject supplier_type_info = config.getJsonObject("SUPPLIER_TYPE_INFO");
                        json.put("supplier_type_name", supplier_type_info.getString(item.getValue("supplier_type").toString()));
                        result_Array.add(json);
                    });
                    promise.complete(CommonUtils.createJsonObject(result_Array)); 
                }
            });  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getSupplierTypeQuery", "数据库连接失败")));
        return promise.future();
	}
	
	/**
	 *  为查询界面挂牌商品状态筛选下拉框，提供有效的商品状态名称和编码
	 *  获取信息表 table_name 中含有的准入
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getListGoodsStatusQuery(PgPool pool, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        String table_name = bodyJSObj.getString("table_name");
        if(table_name.equals("")) {
        	promise.fail(CommonUtils.createExceptionJsonObjectStr(408, "参数 不合法"));
            return promise.future();
        } 
        this.commonRepository.getCon(pool).onSuccess(conn -> {   
            String sql = "select distinct list_goods_status from "+ table_name +" where is_delete=0";
            JsonArray result_Array = new JsonArray();
            this.commonRepository.commonSelectRowsetBySql(conn, sql).onComplete(ar -> {
                if (ar.failed()) {
                	promise.complete(CommonUtils.createExceptionJsonObject(ar, "getListGoodsStatusQuery", "获取挂牌商品状态失败"));
                } else {
                    RowSet<Row> rows = ar.result();
                    rows.forEach(item -> {
                        JsonObject json = new JsonObject();
                        json.put("goods_status", item.getValue("list_goods_status"));
                        JsonObject goods_status_info = config.getJsonObject("GOODS_STATUS_INFO");
                        json.put("goods_status_name", goods_status_info.getString(item.getValue("list_goods_status").toString()));
                        result_Array.add(json);
                    });
                    promise.complete(CommonUtils.createJsonObject(result_Array)); 
                }
            });  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getListGoodsStatusQuery", "数据库连接失败")));
        return promise.future();
	} 
	
	/**
	 *  为查询界面供应链商品状态筛选下拉框，提供有效的商品状态名称和编码
	 *  获取信息表 table_name 中含有的准入
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getSupplyChainGoodsStatusQuery(PgPool pool, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        String table_name = bodyJSObj.getString("table_name");
        if(table_name.equals("")) {
        	promise.fail(CommonUtils.createExceptionJsonObjectStr(408, "参数 不合法"));
            return promise.future();
        } 
        this.commonRepository.getCon(pool).onSuccess(conn -> {   
            String sql = "select distinct supply_chain_goods_status from "+ table_name +" where is_delete=0";
            JsonArray result_Array = new JsonArray();
            this.commonRepository.commonSelectRowsetBySql(conn, sql).onComplete(ar -> {
                if (ar.failed()) {
                	promise.complete(CommonUtils.createExceptionJsonObject(ar, "getSupplyChainGoodsStatusQuery", "获取供应链商品状态失败"));
                } else {
                    RowSet<Row> rows = ar.result();
                    rows.forEach(item -> {
                        JsonObject json = new JsonObject();
                        json.put("goods_status", item.getValue("supply_chain_goods_status"));
                        JsonObject goods_status_info = config.getJsonObject("GOODS_STATUS_INFO");
                        json.put("goods_status_name", goods_status_info.getString(item.getValue("supply_chain_goods_status").toString()));
                        result_Array.add(json);
                    });
                    promise.complete(CommonUtils.createJsonObject(result_Array)); 
                }
            });  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getSupplyChainGoodsStatusQuery", "数据库连接失败")));
        return promise.future();
	} 
	
	
	/**
	 *  为查询界面准入筛选下拉框，提供有效的准入名称和编码
	 *  获取信息表 table_name 中含有的准入
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getAdmitQuery(PgPool pool, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        String table_name = bodyJSObj.getString("table_name");
        if(table_name.equals("")) {
        	promise.fail(CommonUtils.createExceptionJsonObjectStr(408, "参数 不合法"));
            return promise.future();
        } 
        this.commonRepository.getCon(pool).onSuccess(conn -> {   
            String sql = "select distinct is_admit from "+ table_name +" where is_delete=0";
            JsonArray result_Array = new JsonArray();
            this.commonRepository.commonSelectRowsetBySql(conn, sql).onComplete(ar -> {
                if (ar.failed()) {
                	promise.complete(CommonUtils.createExceptionJsonObject(ar, "getAdmitQuery", "获取准入类型失败"));
                } else {
                    RowSet<Row> rows = ar.result();
                    rows.forEach(item -> {
                        JsonObject json = new JsonObject();
                        json.put("is_admit", item.getValue("is_admit"));
                        JsonObject is_admit_info = config.getJsonObject("ADMIT_INFO");
                        json.put("is_admit_name", is_admit_info.getString(item.getValue("is_admit").toString()));
                        result_Array.add(json);
                    });
                    promise.complete(CommonUtils.createJsonObject(result_Array)); 
                }
            });  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getAdmitQuery", "数据库连接失败")));
        return promise.future();
	} 
	
	/**
	 *  为查询界面生效筛选下拉框，提供有效的生效名称和编码
	 *  获取信息表 table_name 中含有的生效
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getDeleteQuery(PgPool pool,JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        String table_name = bodyJSObj.getString("table_name");
        //校验表格名称参数
        if(table_name.equals("")) {
        	promise.fail(CommonUtils.createExceptionJsonObjectStr(408, "参数 不合法"));
            return promise.future();
        } 
        this.commonRepository.getCon(pool).onSuccess(conn -> {   
            String sql = "select distinct is_delete from " + table_name ;
            JsonArray result_Array = new JsonArray();
            this.commonRepository.commonSelectRowsetBySql(conn, sql).onComplete(ar -> {
                if (ar.failed()) {
                	promise.complete(CommonUtils.createExceptionJsonObject(ar, "getDeleteQuery", "获取生效类型失败"));
                } else {
                    RowSet<Row> rows = ar.result();
                    rows.forEach(item -> {
                        JsonObject json = new JsonObject();
                        json.put("is_delete", item.getValue("is_delete"));
                        JsonObject is_delete_info = config.getJsonObject("DELETE_INFO");
                        json.put("is_delete_name", is_delete_info.getString(item.getValue("is_delete").toString()));
                        result_Array.add(json);
                    });
                    promise.complete(CommonUtils.createJsonObject(result_Array)); 
                }
            });  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getDeleteQuery", "数据库连接失败")));
        return promise.future();
	} 
	
	
	/**
	 *  获取外包装物类型名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getOuterPackageType(PgPool pool,JsonObject bodyJSObj) {
       Promise<JsonObject> promise = Promise.promise();
       this.commonRepository.getCon(pool).onSuccess(conn -> { 
       	String sql = "SELECT id, package_type_name from outer_package_type where is_delete=0"; 
       	this.commonRepository.queryJsonArray(conn, sql, "getOuterPackageType") 
       	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
           .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getOuterPackageType", "获取外包装物类型名称和编码失败")) );  
       }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getOuterPackageType", "数据库连接失败")));
       return promise.future();
	}
	
	/**
	 *  为查询界面外包装物类型筛选下拉框，提供有效的外包装物类型等级名称和编码
	 *  获取信息表 table_name 中含有的外包装物类型
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getOuterPackageTypeQuery(PgPool pool,JsonObject bodyJSObj) {
       Promise<JsonObject> promise = Promise.promise();
       JsonObject queryJSObj = new JsonObject();   
       String table_name = bodyJSObj.getString("table_name"); 
       if(table_name.equals("")) {
       	promise.fail(CommonUtils.createExceptionJsonObjectStr(408, "参数 不合法"));
           return promise.future();
       } 
       this.commonRepository.getCon(pool).onSuccess(conn -> {  
           String sql = "select distinct id, package_type_name from outer_package_type where id = any(array( " + createSqlStr("get_outer_package_type", queryJSObj, table_name) + " )) and is_delete=0 ";
           this.commonRepository.queryJsonArray(conn, sql, "getOuterPackageTypeQuery") 
       	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
           .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getOuterPackageTypeQuery", "获取外包装物类型失败")) );  
       }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getOuterPackageTypeQuery", "数据库连接失败")));
       return promise.future();
	} 
	
	/**
	 *  获取外包装物型号名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getOuterPackageMode(PgPool pool,JsonObject bodyJSObj) {
       Promise<JsonObject> promise = Promise.promise();
       this.commonRepository.getCon(pool).onSuccess(conn -> { 
       	String sql = "SELECT  id, package_mode_name from outer_package_mode where is_delete=0"; 
       	this.commonRepository.queryJsonArray(conn, sql, "getOuterPackageMode") 
       	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
           .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getOuterPackageMode", "获取外包装物型号名称和编码失败")) );  
       }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getOuterPackageMode", "数据库连接失败")));
       return promise.future();
	}
	
	/**
	 *  为查询界面外包装物型号筛选下拉框，提供有效的外包装物型号等级名称和编码
	 *  获取信息表 table_name 中含有的外包装物型号
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getOuterPackageModeQuery(PgPool pool,JsonObject bodyJSObj) {
       Promise<JsonObject> promise = Promise.promise();
       JsonObject queryJSObj = new JsonObject();   
       String table_name = bodyJSObj.getString("table_name"); 
       if(table_name.equals("")) {
       	promise.fail(CommonUtils.createExceptionJsonObjectStr(408, "参数 不合法"));
           return promise.future();
       } 
       this.commonRepository.getCon(pool).onSuccess(conn -> {  
           String sql = "select distinct id,package_mode_name from outer_package_mode where id = any(array( " + createSqlStr("get_outer_package_mode", queryJSObj, table_name) + " )) and is_delete=0 ";
           this.commonRepository.queryJsonArray(conn, sql, "getOuterPackageModeQuery") 
       	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
           .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getOuterPackageModeQuery", "获取外包装物型号失败")) );  
       }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getOuterPackageModeQuery", "数据库连接失败")));
       return promise.future();
	} 
	
	/**
	 *  获取内包装物类型名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getInnerPackageType(PgPool pool,JsonObject bodyJSObj) {
       Promise<JsonObject> promise = Promise.promise();
       this.commonRepository.getCon(pool).onSuccess(conn -> { 
       	String sql = "SELECT  id, package_type_name from inner_package_type where is_delete=0"; 
       	this.commonRepository.queryJsonArray(conn, sql, "getInnerPackageType") 
       	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
           .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getInnerPackageType", "获取内包装物类型名称和编码失败")) );  
       }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getInnerPackageType", "数据库连接失败")));
       return promise.future();
	}
	
	/**
	 *  为查询界面内包装物类型筛选下拉框，提供有效的内包装物类型等级名称和编码
	 *  获取信息表 table_name 中含有的内包装物类型
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getInnerPackageTypeQuery(PgPool pool,JsonObject bodyJSObj) {
       Promise<JsonObject> promise = Promise.promise();
       // 验证必须传递的字段
       List<String> must_fields = Arrays.asList("table_name");
       JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
       if (must != null) {
           promise.complete(must);
           return promise.future();
       }  
       JsonObject queryJSObj = new JsonObject();   
       String table_name = bodyJSObj.getString("table_name"); 
       
       this.commonRepository.getCon(pool).onSuccess(conn -> {  
           String sql = "select distinct id, package_type_name from inner_package_type where id = any(array( " + createSqlStr("get_inner_package_type", queryJSObj, table_name) + " )) and is_delete=0 ";
           this.commonRepository.queryJsonArray(conn, sql, "getInnerPackageTypeQuery") 
       	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
           .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getInnerPackageTypeQuery", "获取内包装物类型失败")) );  
       }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getInnerPackageTypeQuery", "数据库连接失败")));
       return promise.future();
	} 
	
	/**
	 *  获取包装物特征类型名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getPackageCharacType(PgPool pool,JsonObject bodyJSObj) {
       Promise<JsonObject> promise = Promise.promise();
       this.commonRepository.getCon(pool).onSuccess(conn -> { 
       	String sql = "SELECT  id, package_charac_type_name from package_charac_type where is_delete=0"; 
       	this.commonRepository.queryJsonArray(conn, sql, "getPackageCharacType") 
       	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
           .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getPackageCharacType", "获取包装物特征类型名称和编码失败")) );  
       }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getPackageCharacType", "数据库连接失败")));
       return promise.future();
	}
	
	/**
	 *  为查询界面包装物特征类型筛选下拉框，提供有效的包装物特征类型等级名称和编码
	 *  获取信息表 table_name 中含有的包装物特征类型
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getPackageCharacTypeQuery(PgPool pool,JsonObject bodyJSObj) {
       Promise<JsonObject> promise = Promise.promise();
       // 验证必须传递的字段
       List<String> must_fields = Arrays.asList("table_name");
       JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
       if (must != null) {
           promise.complete(must);
           return promise.future();
       }  
       JsonObject queryJSObj = new JsonObject();   
       String table_name = bodyJSObj.getString("table_name"); 
        
       this.commonRepository.getCon(pool).onSuccess(conn -> {  
           String sql = "select distinct id,package_charac_type_name from package_charac_type where id = any(array( " + createSqlStr("get_package_charac_type", queryJSObj, table_name) + " )) and is_delete=0 ";
           this.commonRepository.queryJsonArray(conn, sql, "getPackageCharacTypeQuery") 
       	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
           .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getPackageCharacTypeQuery", "获取包装物特征类型失败")) );  
       }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getPackageCharacTypeQuery", "数据库连接失败")));
       return promise.future();
	} 
	
	/**
	 *  获取车型名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getTruckType(PgPool pool,JsonObject bodyJSObj) {
       Promise<JsonObject> promise = Promise.promise();
       this.commonRepository.getCon(pool).onSuccess(conn -> { 
       	String sql = "SELECT distinct id, type_name from truck_type where is_delete=0"; 
       	this.commonRepository.queryJsonArray(conn, sql, "getTruckType") 
       	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
           .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getTruckType", "获取车型名称和编码失败")) );  
       }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getTruckType", "数据库连接失败")));
       return promise.future();
	}
	
	/**
	 *  为查询界面车型筛选下拉框，提供有效的车型等级名称和编码
	 *  获取信息表 table_name 中含有的车型
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getTruckTypeQuery(PgPool pool,JsonObject bodyJSObj) {
       Promise<JsonObject> promise = Promise.promise();
       // 验证必须传递的字段
       List<String> must_fields = Arrays.asList("table_name");
       JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
       if (must != null) {
           promise.complete(must);
           return promise.future();
       }   
       JsonObject queryJSObj = new JsonObject();   
       String table_name = bodyJSObj.getString("table_name");  
       this.commonRepository.getCon(pool).onSuccess(conn -> {  
           String sql = "select distinct id,type_name from truck_type where id = any(array( " + createSqlStr("get_truck_type", queryJSObj, table_name) + " )) and is_delete=0 ";
           this.commonRepository.queryJsonArray(conn, sql, "getTruckTypeQuery") 
       	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
           .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getTruckTypeQuery", "获取车型失败")) );  
       }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getTruckTypeQuery", "数据库连接失败")));
       return promise.future();
	} 
	
	/**
	 *  获取户籍地区名称和编码
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getResidenceArea(PgPool pool,JsonObject bodyJSObj) {
       Promise<JsonObject> promise = Promise.promise();
       this.commonRepository.getCon(pool).onSuccess(conn -> { 
       	String sql = "SELECT  id, area_name from administrative_area where is_delete=0"; 
       	this.commonRepository.queryJsonArray(conn, sql, "getResidenceArea") 
       	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
           .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getResidenceArea", "获取户籍地区名称和编码失败")) );  
       }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getResidenceArea", "数据库连接失败")));
       return promise.future();
	}
	
	/**
	 *  为查询界面户籍地区筛选下拉框，提供有效的户籍地区等级名称和编码
	 *  获取信息表 table_name 中含有的户籍地区
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getResidenceAreaQuery(PgPool pool,JsonObject bodyJSObj) {
       Promise<JsonObject> promise = Promise.promise();
       // 验证必须传递的字段
       List<String> must_fields = Arrays.asList("table_name");
       JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
       if (must != null) {
           promise.complete(must);
           return promise.future();
       }   
       JsonObject queryJSObj = new JsonObject();   
       String table_name = bodyJSObj.getString("table_name"); 
        
       this.commonRepository.getCon(pool).onSuccess(conn -> {  
           String sql = "select distinct id,area_name from administrative_area where id = any(array( " + createSqlStr("get_administrative_area", queryJSObj, table_name) + " )) and is_delete=0 ";
           this.commonRepository.queryJsonArray(conn, sql, "getResidenceAreaQuery") 
       	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
           .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getResidenceAreaQuery", "获取户籍地区失败")) );  
       }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getResidenceAreaQuery", "数据库连接失败")));
       return promise.future();
	} 
	
	/**
	 *  为查询界面司机筛选下拉框，提供有效的司机名称
	 *  获取信息表 table_name 中含有的司机
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getCarrierDriverQuery(PgPool pool,JsonObject bodyJSObj) {
       Promise<JsonObject> promise = Promise.promise();
       // 验证必须传递的字段
       List<String> must_fields = Arrays.asList("table_name");
       JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
       if (must != null) {
           promise.complete(must);
           return promise.future();
       }    
       String table_name = bodyJSObj.getString("table_name"); 
       
       this.commonRepository.getCon(pool).onSuccess(conn -> {  
           String sql = "select distinct driver_name from " + table_name   ;
           this.commonRepository.queryJsonArray(conn, sql, "getCarrierDriverQuery") 
       	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
           .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getCarrierDriverQuery", "获取司机失败")) );  
       }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getCarrierDriverQuery", "数据库连接失败")));
       return promise.future();
	} 
	
	/**
	 *  为查询界面支付主体筛选下拉框，提供有效的支付主体名称和编码
	 *  获取信息表 table_name 中含有的户籍地区
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getLegalEntityQuery(PgPool pool,JsonObject bodyJSObj) {
       Promise<JsonObject> promise = Promise.promise();
       // 验证必须传递的字段
       List<String> must_fields = Arrays.asList("table_name");
       JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
       if (must != null) {
           promise.complete(must);
           return promise.future();
       }  
       String table_name = bodyJSObj.getString("table_name"); 
        
       JsonObject queryJSObj = new JsonObject();
       this.commonRepository.getCon(pool).onSuccess(conn -> {  
           String sql = "select distinct id,legal_entity_name from " + table_name + " where id = any(array( " + createSqlStr("getLegalEntityQuery", queryJSObj, table_name) + " )) ";
           this.commonRepository.queryJsonArray(conn, sql, "getPaymentEntityQuery") 
       	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
           .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getLegalEntityQuery", "获取支付主体失败")) );  
       }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getLegalEntityQuery", "数据库连接失败")));
       return promise.future();
	} 
	
	/**
	 *  为查询界面报损类型筛选下拉框，提供有效的报损类型名称和编码
	 *  获取信息表 table_name 中含有的户籍地区
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getLossTypeQuery(PgPool pool,JsonObject bodyJSObj) {
       Promise<JsonObject> promise = Promise.promise();
       // 验证必须传递的字段
       List<String> must_fields = Arrays.asList("table_name");
       JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
       if (must != null) {
           promise.complete(must);
           return promise.future();
       }  
       String table_name = bodyJSObj.getString("table_name");  
       JsonObject queryJSObj = new JsonObject();
       this.commonRepository.getCon(pool).onSuccess(conn -> {  
           String sql = "select distinct loss_type_name from " + table_name + " where loss_type_name = any(array( " + createSqlStr("get_loss_type", queryJSObj, table_name) + " ))   ";
           this.commonRepository.queryJsonArray(conn, sql, "getLossTypeQuery") 
       	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
           .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getLossTypeQuery", "获取报损类型失败")) );  
       }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getLossTypeQuery", "数据库连接失败")));
       return promise.future();
	} 
	
	/**
	 *  为用户管理查询界面用户手机号下拉框，提供有效的手机号
	 *  获取信息表 table_name 中含有的供应商
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getPhoneQuery(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证必须传递的字段
        List<String> must_fields = Arrays.asList("table_name");
        JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
        if (must != null) {
            promise.complete(must);
            return promise.future();
        }  
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	JsonObject queryJSObj = new JsonObject();   
            String table_name = bodyJSObj.getString("table_name");
            String sql = "";
            if(!table_name.equals("")) //用于除供应商基本信息页面外的其它页面中 供应商筛选下拉框
            	sql = "select distinct phone from users where phone = any(array( " + createSqlStr("get_phone", queryJSObj, table_name) + " )) and is_delete=0 ";
            else  //用于供应商基本信息页面 供应商筛选下拉框
            	sql = "select distinct phone from users";
            this.commonRepository.queryJsonArray(conn, sql, "getPhoneQuery") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getPhoneQuery", "获取手机号失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getPhoneQuery", "数据库连接失败")));
        return promise.future();
	} 
	 
	/**
	 *  为用户管理查询界面用户名下拉框，提供有效的用户名
	 *  获取信息表 table_name 中含有的供应商
	 * @param pool
	 * @param bodyJSObj
	 * @return
	 */
	public Future<JsonObject> getUserNameQuery(PgPool pool,JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 验证必须传递的字段
        List<String> must_fields = Arrays.asList("table_name");
        JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
        if (must != null) {
            promise.complete(must);
            return promise.future();
        }  
        this.commonRepository.getCon(pool).onSuccess(conn -> { 
        	JsonObject queryJSObj = new JsonObject();   
            String table_name = bodyJSObj.getString("table_name");
            String sql = "";
            if(!table_name.equals("")) //用于除供应商基本信息页面外的其它页面中 供应商筛选下拉框
            	sql = "select distinct user_name from users where user_name = any(array( " + createSqlStr("get_user_name", queryJSObj, table_name) + " )) and is_delete=0 ";
            else  //用于供应商基本信息页面 供应商筛选下拉框
            	sql = "select distinct user_name from users";
            this.commonRepository.queryJsonArray(conn, sql, "getPhoneQuery") 
        	.onSuccess(re -> promise.complete(CommonUtils.createJsonObject(re)))
            .onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getUserNameQuery", "获取用户名失败")) );  
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getUserNameQuery", "数据库连接失败")));
        return promise.future();
	} 
	

}
